package bdd.daos.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bdd.beans.DBLink;
import bdd.beans.Indisponibilites;
import bdd.beans.Note;
import bdd.daos.DAOEnseignant;
import bdd.daos.DAOEtudiant;
import bdd.daos.DAOExamen;
import bdd.daos.DAONote;

public class DAONoteImpl implements DAONote {
	private DBLink dblink;

	@Override
	public Note save(Note newInstance) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Note find(Integer id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Note> findAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void update(Note transientObject) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(Note persistentObject) {
		// TODO Auto-generated method stub

	}

	public List<Note> findByEcEnseignantPromotion(Integer numEc, Integer numEnseignant, Integer numPromotion) {
		List<Note> liste = new ArrayList();
		dblink.connexion();
		
		String query = "SELECT NOTE.NUMETUDIANT, NOTE.NOTE, NOTE.NUMEXAMEN, EXAMEN.LIBELLE " +
				"FROM NOTE,ENSEIGNANT,PROMOTION,EC,JNTTYPEECENSEIGNANT,JNTETUDIANTPROMOTION, EXAMEN " +
				"WHERE EC.NUMEC=JNTTYPEECENSEIGNANT.NUMEC " +
				"AND JNTTYPEECENSEIGNANT.NUMENSEIGNANT = ENSEIGNANT.NUMENSEIGNANT " +
				"AND NOTE.NUMETUDIANT = JNTETUDIANTPROMOTION.NUMETUDIANT " +
				"AND PROMOTION.NUMPROMOTION = JNTETUDIANTPROMOTION.NUMPROMOTION " +
				"AND EC.NUMEC = " + numEc + " " +
				"AND ENSEIGNANT.NUMENSEIGNANT = " + numEnseignant + " " +
				"AND PROMOTION.NUMPROMOTION = " + numPromotion + " " + 
				"AND NOTE.NUMEXAMEN = EXAMEN.NUMEXAMEN " +
				"ORDER BY NOTE.NUMEXAMEN";
		
		try {
			PreparedStatement pst = dblink.getConnection().prepareStatement(query); 
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()) {
				Note n = new Note();
				n.setNote(rs.getDouble("NOTE"));

				DAOEtudiant daoEtudiant = new DAOEtudiantImpl();
				n.setEtudiant(daoEtudiant.find(rs.getInt("NUMETUDIANT")));
				DAOExamen daoExamen = new DAOExamenImpl();
				n.setExamen(daoExamen.find(rs.getInt("NUMEXAMEN")));
				
				liste.add(n);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			dblink.deconnexion();
		}
		
		return liste;
	}

}
